	DECLARE @Duration int
	SET @Duration = 12

DECLARE @Year INT
DECLARE @Month INT

SET @Year = 2010
SET @Month = 12

CREATE TABLE #tblMonthlyRevenue
(
	ID INT IDENTITY(1, 1)
	,[Month] INT
	,[Year] INT
	,[DoanhSo] MONEY
	,KhachHangID UNIQUEIDENTIFIER
	,ChuongTrinhID UNIQUEIDENTIFIER
	,LoaiSanPham nvarchar(100)
)

CREATE TABLE #tblQuarterlyRevenue
(
	ID INT IDENTITY(1, 1)
	,Quarter nvarchar(100)
	,[DoanhSo] MONEY
	,KhachHangID UNIQUEIDENTIFIER
	,ChuongTrinhID UNIQUEIDENTIFIER
	,LoaiSanPham nvarchar(100)
)

	INSERT INTO #tblMonthlyRevenue(KhachHangID, [Month], [Year], [DoanhSo], [LoaiSanPham], [ChuongTrinhID])
	SELECT mr.KhachHangID
	, [Thang], [Nam]
	, DoanhSoTD as DoanhSo
	, LoaiSanPham
	, kh.ID
	FROM vwGetTotalMonthlyRevenue mr
	inner join KhachHangDuThuong kh ON mr.KhachHangID = kh.HoiVienID
	AND Duration=@Duration
	--INNER JOIN ThuongHieu th ON th.BrandID = kh.BrandID
	--INNER JOIN DoanhSoCamKet ds ON ds.KhachHangDuThuongID = kh.HoiVienID AND ds.[Month] = DATEDIFF(month, StartDate, CAST((cast(Nam as nvarchar(4)) + '-' + CAST(Thang as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME))
	WHERE CAST((cast(Nam as nvarchar(4)) + '-' + CAST(Thang as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME) BETWEEN StartDate AND EndDate
	AND CAST((cast(@Year as nvarchar(4)) + '-' + CAST(@Month as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME) BETWEEN StartDate AND EndDate
	--AND @BrandID = kh.BrandID
	--AND CHARINDEX('TD', LoaiSanPham) > 0

	DECLARE @ChuongTrinhID UNIQUEIDENTIFIER
	DECLARE DoanhSoCursor CURSOR FOR SELECT ID FROM KhachHangDuThuong
	Open DoanhSoCursor

	FETCH NEXT FROM DoanhSoCursor
	INTO @ChuongTrinhID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE @StartDate DATETIME
		DECLARE @EndDate DateTime
		DECLARE @KhachHangID UNIQUEIDENTIFIER
		DECLARE @LoaiSanPham nvarchar(100)

		SELECT @StartDate = StartDate, @KhachHangID = HoiVienID, @LoaiSanPham = LoaiSanPham
		FROM KhachHangDuThuong WHERE ID = @ChuongTrinhID

		SET @EndDate = @StartDate
		SET @EndDate = DATEADD(month,12,  @EndDate)

		DECLARE @Quarter INT
		SET @Quarter = 1

		WHILE @StartDate < @EndDate
		BEGIN
			DECLARE @tempEndDate DATETIME
			SET @tempEndDate = DATEADD(month, 3, @StartDate)

			INSERT INTO #tblQuarterlyRevenue(KhachHangID, [Quarter], [DoanhSo], [LoaiSanPham], [ChuongTrinhID])
			SELECT @KhachHangID, RTRIM(@LoaiSanPham) + CAST(@Quarter as nvarchar(1)), ISNULL(SUM(DoanhSo), 0), @LoaiSanPham, @ChuongTrinhID
			FROM #tblMonthlyRevenue
			WHERE ChuongTrinhID = @ChuongTrinhID
			AND @startDate <= CAST((cast([Year] as nvarchar(4)) + '-' + CAST([Month] as nvarchar(2)) +'-' + CAST(DATEPART(day, @StartDate) as nvarchar(2))) as DATETIME)
			AND @tempEndDate > CAST((cast([Year] as nvarchar(4)) + '-' + CAST([Month] as nvarchar(2)) +'-' + CAST(DATEPART(day, @StartDate) as nvarchar(2))) as DATETIME)
			
			SET @StartDate = @tempEndDate
			
			SET @Quarter = @Quarter + 1
		END
		FETCH NEXT FROM DoanhSoCursor
		INTO @ChuongTrinhID
	END


SELECT * FROM (
	SELECT KhachHangID, ChuongTrinhID, LoaiSanPham, DoanhSo, Quarter from #tblQuarterlyRevenue
	) AS TDTable
	pivot (SUM (DoanhSo) FOR Quarter IN ([TD1], [TD2], [TD3], [TD4], [CC1], [CC2], [CC3], [CC4])) as pvt 

CLOSE DoanhSoCursor;
DEALLOCATE DoanhSoCursor;
DROP TABLE #tblQuarterlyRevenue
DROP TABLE #tblMonthlyRevenue
